from utils.basic_function import *

class MatchAccuracyAssess():
    def __init__(self):
        self.NEW_LDBM_COL = 'match_ldbm'
        self.MATCH_COL = MATCH_COL

    def no_match_road(self, rp_table, match_table):
        """
        没有匹配结果的上报路段
        Args:
            rp_table:
            match_table:

        Returns:

        """
        with conn:
            with conn.cursor() as cursor:
                sql = f"""
                    drop table if exists no_match_{rp_table[-1]};
                    create table no_match_{rp_table[-1]} as(
                    select * from {rp_table} where {self.NEW_LDBM_COL} not in 
                        (select {MATCH_COL} from {match_table}))"""
                cursor.execute(sql)

    def partly_match_road(self, rp_table, match_table, sim_threshold):
        """
        部分匹配的上报路段
        Args:
            rp_table:
            match_table:

        Returns:
            新增字段： rp_buffer_sim float 缓冲区相似度

        """
        cur = conn.cursor()

        sql = f"""alter table {rp_table} add column if not exists rp_buffer_sim float ;"""
        cur.execute(sql)

        sql = f"""select distinct match_road from {match_table} order by match_road;"""
        rp_ldbm_list = pd.read_sql(sql, conn).values.flatten()
        for rp_ldbm in rp_ldbm_list:
            buffer_sim = self.rp_buffer_sim(rp_table, match_table, 30, rp_ldbm)
            sql = f"""update {rp_table} set rp_buffer_sim = {buffer_sim} where {self.NEW_LDBM_COL} = '{rp_ldbm}'"""
            cur.execute(sql)
        conn.commit()

        with conn:
            with conn.cursor() as cursor:
                sql = f"""
                            drop table if exists partly_match_{rp_table[-1]};
                            create table partly_match_{rp_table[-1]} as (
                                select * from {rp_table} where rp_buffer_sim < {sim_threshold})"""
                cursor.execute(sql)

    def rp_buffer_sim(self,rp_table, match_table, BUFFER_DIST, rp_ldbm):
        """缓冲区重叠度=相交缓冲区/上报缓冲区"""
        cur = conn.cursor()

        # 匹配路段缓冲区
        sql = f"""
                  drop table if exists tmp_match_buffer;
                  create temporary table tmp_match_buffer as 
                              (select {GEOM_COL} as line_geom from {match_table} where {MATCH_COL} = '{rp_ldbm}');
                  """
        cur.execute(sql)
        postgis_add_geomcol('tmp_match_buffer', conn, SRID, 'polygon')
        sql = f"""
                    update tmp_match_buffer set geom = ST_Buffer(line_geom, {BUFFER_DIST});
                    -- 合并遥感路网缓冲区
                    drop table if exists tmp_union_match_buffer;
                    create table tmp_union_match_buffer as (
                                  select st_union(ARRAY(select geom from tmp_match_buffer)) as geom);"""
        cur.execute(sql)

        # 上报路段缓冲区
        sql = f"""
                  drop table if exists tmp_sb_buffer;
                  create table tmp_sb_buffer as (
                                  select geom as line_geom 
                                    from {rp_table} where {self.NEW_LDBM_COL} = '{rp_ldbm}')"""
        cur.execute(sql)
        postgis_add_geomcol('tmp_sb_buffer', conn, SRID, 'polygon')
        sql = f"""update tmp_sb_buffer set geom = ST_Buffer(line_geom, {BUFFER_DIST});"""
        cur.execute(sql)
        conn.commit()

        try:
            sql = f"""
                        -- 裁剪示例范围内
                        drop table if exists tmp;
                        create table tmp as (select st_intersection(a.geom, b.geom) as geom from tmp_sb_buffer a, study_area_utm b);
                        update tmp_sb_buffer a set geom = (select st_union(ARRAY(select geom from tmp)) as geom)"""
            cur.execute(sql)
        except:
            conn.commit()
            pass

        # 相交缓冲区/上报缓冲区
        sql = f"""select st_area(st_intersection(a.geom, b.geom)) from tmp_union_match_buffer a, tmp_sb_buffer b"""
        intersect_area = pd.read_sql(sql, conn).values.flatten()[0]
        sql = f"""select st_area(geom) from tmp_sb_buffer"""
        sb_area = pd.read_sql(sql, conn).values.flatten()[0]

        return intersect_area / sb_area



